<?php
require_once('gbif.checklist.php');
class Checklist12 extends Checklist{

	public $lookup;

	function __construct( $file = NULL ) {
		parent::__construct($file);
	}

	public function buildLookUp ($sheetIndex = 2 ) {
		if (!$this->fileOpen) $this->openFile();
		$wk = $this->objPHPExcel->setActiveSheetIndex( $sheetIndex );
		foreach ($wk->getRowIterator() as $row) {
			$cellIterator = $row->getCellIterator();
			$cellIterator->setIterateOnlyExistingCells(true);
			$index = -1;
			foreach ($cellIterator as $cell) {
				$index++;
				$value = $cell->getValue();
				$value = str_replace("\x0B", "", $value);  //remove all vertical tabs in sender string
				$value = trim($value);
				$this->lookup[$value] = $index;
			}
			break;
		}

		$index = 1;
		while( isset($this->lookup[ 'Taxon' . $index ]) ) {
		$index++;
		}
		$this->taxonIndex = $index-1;
	}

	public function validateChecklistData( $sheetIndex = 2 ) {
		if (!$this->fileOpen) $this->openExcel();

		$passed = true;
		$wk = $this->objPHPExcel->setActiveSheetIndex( $sheetIndex );

		$highestRow = $wk->getHighestRow();
		$highestColumn = $wk->getHighestColumn();
		$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

		$rowIndex = -1;
		# Builds array but removes any blank rows
		foreach ($wk->getRowIterator() as $row) {
			$rowIndex++;
			$blankRow = true;

			$cellIterator = $row->getCellIterator();
			$cellIterator->setIterateOnlyExistingCells(false);
			$colIndex = -1;
			foreach ($cellIterator as $cell) {
				$colIndex++;
				$value = $cell->getValue();

				# Check that data do not contain control characters that break the XML transfer format (e.g. vertical tabs).  Remove if included.
				$value = str_replace("\x0B", "", $value);  //remove all vertical tabs in sender string

				$this->checklistArray[$rowIndex][$colIndex] = trim($value);
				if ($this->checklistArray[$rowIndex][$colIndex] != "") {
					$blankRow = false;
				}
			}
			if ($blankRow || $rowIndex == 0) {
				unset($this->checklistArray[$rowIndex]);
			} else {
				if ($rowIndex > 0) {
					for($i=1;$i<=$this->taxonIndex;$i++) {
						$tmpObj->{'Taxon'.$i} = $this->checklistArray[$rowIndex][$this->lookup['Taxon'.$i]];
					}
					$tmpObj->authorship = $this->checklistArray[$rowIndex][$this->lookup['Authorship']];
					$tmpObj->rank = $this->checklistArray[$rowIndex][$this->lookup['Rank']];
					$tmpObj->code = $this->checklistArray[$rowIndex][$this->lookup['Code']];
					$tmpObj->commonName = $this->checklistArray[$rowIndex][$this->lookup['Common Names']];
					$tmpObj->synonyms = $this->checklistArray[$rowIndex][$this->lookup['Synonyms']];
					$tmpObj->countries = $this->checklistArray[$rowIndex][$this->lookup['Countries']];
					$tmpObj->threatStatus = $this->checklistArray[$rowIndex][$this->lookup['Threat Status']];
					$tmpObj->yearEvaluated = $this->checklistArray[$rowIndex][$this->lookup['Year Evaluated']];
					$tmpObj->nativeStatus = $this->checklistArray[$rowIndex][$this->lookup['Native Status']];

					if($tmpObj->rank != 'species') {
						$this->addTaxa( $tmpObj );
					}
				}
			}
		}
		return( $passed );
	}

	public function writechecklistData($path = '') {
		$fp = fopen($path . $this->checklistFilename, 'w');
		fputs($fp, "\xEF\xBB\xBF"); 
		fputcsv($fp, $this->checklistOutputHeaderList );

		$fpVernacular = fopen( $path . $this->vernacularFilename, 'w');
		fputs($fpVernacular, "\xEF\xBB\xBF"); 
		fputcsv($fpVernacular, $this->vernacularHeaderList);

		$fpDistribution = fopen( $path . $this->distributionFilename, 'w');
		fputs($fpDistribution, "\xEF\xBB\xBF"); 
		fputcsv($fpDistribution, $this->distributionHeaderList);

		if(is_array($this->taxa)) {
		foreach($this->taxa as $rec) {
			fputcsv($fp, array(
					$rec['index']
				,	$this->taxa[$rec['parent']]['index']
				,	""
				,	$rec['value']
				,	$rec['authorship']
				,	$rec['code']
				,	$rec['rank']
			));

			if ($rec['commonName'] != '') {
				$this->writeVernacularData($fpVernacular, $rec['index'], $rec['commonName']);
			}

			# Add Synonyms
			if (trim($this->lookup['Synonyms']) != '') {
				$this->writeSynonymData( $fp, $rec['index'], $row['synonyms'] );
			}

			// If Distribution names exist send to file to be added
			if ((trim($row['countries']) != '') 
				|| (trim($row['threatStatus']) != '')
				|| (trim($row['yearEvaluated']) != '')
				|| (trim($row['nativeStatus']) != '')
			){
				$this->writeDistributionData($fpDistribution, $rec['index'], array('countries' => $row['countries'], 'delimiter' => '|'), $row['threatStatus'], $row['yearEvaluated'], $row['nativeStatus']);
			}

		}
		}
#print "<pre>";		
#print_r($this->taxa);
		if(is_array($this->checklistArray)) {
		foreach ($this->checklistArray as $row) {
			$ind = "";
			// Lookup Parent ID
			$lastColumnIndex = $this->findLastColumn($row);
			for($i=$lastColumnIndex - 1; $i>0; $i--){
				$ind .= $row[$this->lookup['Taxon'.$i]];
			}
#			print "::" . $ind . "::<br>";
			$parent = $this->taxa[$ind];
			
			// Write species to file
			if ($row[$this->lookup["Rank"]] == 'species') {

				$tmpSpecies = array(
						'sp' . $this->speciesIndex++
					,	$parent['index']
					,	''
					,	$row[$this->lookup['Taxon' . $lastColumnIndex]]
					,	$row[$this->lookup['Authorship']]
					,	$row[$this->lookup['Code']]
					,	$row[$this->lookup["Rank"]]
					,	''
					,	$row[$this->lookup['Reference']]
				);

				fputcsv($fp, $tmpSpecies );
#print_r( $tmpSpecies);
				// If common name exists send to vernacular file to be added
				if ($row[$this->lookup['Common Names']] != '') {
					$tmpIndex = $parent['index'];
					if ($row[$this->lookup['Taxon' . $lastColumnIndex]] != '') {
						$tmpIndex = 'sp' . ($this->speciesIndex - 1);
					}
						
					$this->writeVernacularData($fpVernacular, $tmpIndex, $row[$this->lookup['Common Names']]);
				}

				# Add Synonyms
				if (trim($this->lookup['Synonyms']) != '') {
					$this->writeSynonymData( $fp, 'sp' . ($this->speciesIndex - 1), $row[$this->lookup['Synonyms']] );
				}

				// If Distribution names exist send to file to be added
				if ((trim($row[$this->lookup['Countries']]) != '') 
					|| (trim($row[$this->lookup['Threat Status']]) != '')
					|| (trim($row[$this->lookup['Year Evaluated']]) != '')
					|| (trim($row[$this->lookup['Native Status']]) != '')
				){
					$this->writeDistributionData($fpDistribution, 'sp' . ($this->speciesIndex - 1), array('countries' => $row[$this->lookup['Countries']], 'delimiter' => '|'), $row[$this->lookup['Threat Status']], $row[$this->lookup['Year Evaluated']], $row[$this->lookup['Native Status']]);
				}

			}			
		}
		} # foreach checklist row
			
		fclose($fp);
		fclose($fpVernacular);
		fclose($fpDistribution);
	}

	private function findLastColumn($row) {
		for($i=$this->taxonIndex; $i>0; $i--){
			if (($row[$this->lookup['Taxon'.$i]] != '')) {
				return($i);
			}
		}		
	}

	public function addTaxa( $rec ) {
		static $index = 1;

		$parent = '';
		for($i=1;$i<=$this->taxonIndex;$i++) {			
			if ($rec->{'Taxon'.$i} != '') {
#				print $rec->{'Taxon'.$i} . $parent . " - $i<br>";
				if (!isset( $this->taxa[$rec->{'Taxon'.$i} . $parent] ) ) {
					$this->taxa[ $rec->{'Taxon'.$i} . $parent ] = array(
							'rank' => $rec->rank
						,	'parent' => $parent
						,	'value' => $rec->{'Taxon'.$i}
						,	'index' => $index++
						,	'authorship' => $rec->authorship
						,	'code' => $rec->code
						,	'commonName' => $rec->commonName
						,	'synonyms' => $rec->synonyms
						,	'countries' => $rec->countries
						,	'threatStatus' => $rec->threatStatus
						,	'yearEvaluated' => $rec->yearEvaluated
						,	'nativeStatus' => $rec->nativeStatus
					);
				}
			}
			$parent = $rec->{'Taxon'.$i} . $parent;
		}
	}

}
?>